const mysql = require('mysql2');
// 创建数据池
const dbConfig = require('./db.config');
// 创建连接池
const pool = mysql.createPool({
    ...dbConfig
});
// 执行语句
function execute(sql, statement = []) {
    let connPromise = pool.promise();
    return connPromise.execute(sql, statement);
}
// 
// 新增数据
async function insert(tablename, statement) {
    let statementkeys = Object.keys(statement);
    let val_tmp = statementkeys.length == 1 ? "?" : ('?,'.repeat(statementkeys.length - 1) + '?');
    let sql = `insert into ${tablename} (${statementkeys.join(',')}) values(${val_tmp}) `;
    console.log(sql)
    return (await execute(sql, Object.values(statement)))[0];
}
// 根据表名查询
async function selectByTable(tablename, fields = '*') {
    fields = fields == '*' ? fields : fields.join(',');
    let sql = `select ${fields} from ${tablename}`;
    let result = await execute(sql, []);
    return result[0];
}

// 删除 obj={id:123}, obj={name:'aaa'}
async function del(tablename, obj = {}) {
    let key_ = Object.keys(obj)[0];
    let sql = `delete from ${tablename} where ${key_}=?`;
    let result = await execute(sql, [obj[key_]]);
    return result[0];
}
// 
// 查询全部
// async function selectAll(tablename, opt = { fileds: '*', where_and: ['id=123', 'name=234'] }) {
async function select(tablename, { fields = '*', where_and = [], where_or = [] }) {
    let fields_ = fields == '*' ? fields : fields.join(',')
    let where_and_ = where_and.join(' and ');
    let where_or_ = where_or.join(' or ');
    let where_ = where_and_ + where_or_;
    where_ = where_ == '' ? '' : ' where ' + where_;
    let sql = `select ${fields_} from ${tablename} ` + where_;
    console.log(sql);
    return (await execute(sql, []))[0];
}
// 查询分页数据
// opt={pagesize:10,pageindex:1,fields:['id','name']}
async function selectByPage(tablename, { pagesize = 10, pageindex = 1, fields = '*' }) {
    let fields_ = fields == '*' ? fields : fields.join(',')
    let sql = `select ${fields_} from ${tablename} limit ?,?`;
    return (await execute(sql, [pagesize * (pageindex - 1), pagesize]))[0];
}
// 更新数据
async function update(tablename, { updateFields = {}, where_and = [], where_or = [] }) {
    let where_and_ = where_and.join(' and ');
    let where_or_ = where_or.join(' or ');
    let where_ = where_and_ + where_or_;
    where_ = where_ == '' ? '' : ' where ' + where_;
    let keyValues = [];
    for (let key in updateFields) {
        keyValues.push(`${key}=${updateFields[key]}`);
    }
    let setValues = keyValues.join(', ');
    let sql = `update ${tablename} set ${setValues} ${where_}`;
    console.log(sql);
    return (await execute(sql, []))[0];
}
module.exports = { execute, insert, selectByTable, del, select, selectByPage, update };
